﻿// mysqlAPI.cpp: 定义应用程序的入口点。
//
#include "mysqlAPI.h"
using namespace std;

MySqlAPI::MySqlAPI()
{
	driver = sql::mysql::get_mysql_driver_instance();
}

MySqlAPI::~MySqlAPI()
{
	releaseAllSavePoints();

	if (stmt = nullptr) {
		stmt->close();
		delete stmt;
	}
	if (conn != nullptr)
	{
		conn->close();
		delete conn;
	}
	if (driver != nullptr)
		delete driver;
}

int MySqlAPI::connect(const std::string& hostName, const std::string& userName,
	const std::string& password, const bool autocommit)
{
	try {
		conn = driver->connect(hostName.c_str(), userName.c_str(), password.c_str());
		infoprintf("连接成功,关闭自动commit\n");
		conn->setAutoCommit(autocommit);
		_autocommit = autocommit;
	}catch (sql::SQLException& ex)
	{
		errorprintf(" 连接失败,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		return -1;
	}
	return 0;
}

int MySqlAPI::selectDataBase(const std::string& database)
{
	try {
		if (stmt != nullptr)
		{
			if(!_iscommitted)//判断上一个数据库是否已提交
				commit();//没提交的操作需要提交保存
			stmt->close();
			delete stmt;
			stmt = nullptr;
		}

		conn->setSchema(database.c_str());
		stmt = conn->createStatement();
		stmt->execute("SET character set utf8");
		_current_databasename = database;
	}catch (sql::SQLException& ex)
	{
		errorprintf(" 数据库选择失败,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		return -1;
	}
	infoprintf( "数据库选择成功\n");
	return 0;
}


int MySqlAPI::selectTable(const std::string& tablename)
{
	_current_tablename = tablename;
	return getCurrentTablePropertys();
}

int MySqlAPI::sortTableById(const std::string& tablename, bool force_commit)
{
	if (!_iscommitted)
	{
		if (force_commit)
		{
			warnprintf("********数据库尚未提交，不能执行排序，用户已使能强制提交********\n");
			commit();
		}
		else
		{
			errorprintf("********数据库尚未提交，不能执行排序，否则会丢失所有保存点和已有改动********\n");
			return -1;
		}
		
	}
	infoprintf("重新排序...\n");
	std::string qurey = "set @i=0;";
	stmt->executeUpdate(qurey.c_str());
	qurey = "update " + tablename + " set id =(@i:=@i+1);";
	int rowcount = stmt->executeUpdate(qurey.c_str());
	qurey = "alter table " + tablename + " AUTO_INCREMENT=0;";
	stmt->executeUpdate(qurey.c_str());

	infoprintf("重新排序完成，影响行数：%d...\n", rowcount);
	return 0;
}

int MySqlAPI::getTableRowCount(int& rowcount)
{
	rowcount = -1;
	sql::ResultSet *res = nullptr;
	try
	{
		std::string query = "select count(*) from " + _current_tablename;
		res = stmt->executeQuery(query.c_str());
		if (res->next())
		{
			rowcount = res->getInt(1);
			infoprintf("current table row count:%d\n", rowcount);
		}
	}
	catch (sql::SQLException& ex)
	{
		delete res;
		errorprintf(" getTableRowCount failed,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		return -1;
	}
	catch (std::exception& ex)
	{
		delete res;
		errorprintf(" getTableRowCount failed,std::exception occured,error message: %s\n", ex.what());
		return -1;
	}

	delete res;
	res = nullptr;
	return 0;
}

int MySqlAPI::setSavePoint(std::string savepoint_str) {
	try { 
		sql::Savepoint* savepoint = conn->setSavepoint(savepoint_str.c_str());
		savepoint_list.push_back(savepoint);
		infoprintf("save point name: %s\n", savepoint_str.c_str());
		_iscommitted = false;
	}
	catch (sql::SQLException& ex)
	{
		errorprintf(" setSavePoint failed,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		return -1;
	}
	catch (std::exception& ex)
	{
		errorprintf(" setSavePoint failed,std::exception occured,error message: %s\n", ex.what());
		return -1;
	}
	return 0;
}

int MySqlAPI::rollBackToSavePoint(sql::Savepoint* ptr) {
	if (_iscommitted)
	{
		infoprintf("********数据库已修改，已提交，则所有回滚点与保存点都没有用了********\n");
		releaseAllSavePoints();
		return 0;
	}

	std::vector<sql::Savepoint*>::iterator target_iter = std::find(savepoint_list.begin(), savepoint_list.end(), ptr);
	if (target_iter == savepoint_list.end())
	{
		errorprintf(" rollBackToSavePoint failed,savepoint not existing\n");
		return -1;
	}

	try {
		while(savepoint_list.back() != *target_iter){
			conn->rollback(savepoint_list.back());
			conn->releaseSavepoint(savepoint_list.back());
			savepoint_list.pop_back();
		}
		conn->rollback(*target_iter);
		conn->releaseSavepoint(*target_iter);
		savepoint_list.pop_back();
		infoprintf("rollBackToSavePoint ok,savepoints remain :%d\n",savepoint_list.size());
	}
	catch (sql::SQLException& ex)
	{
		errorprintf(" setSavePoint failed,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		return -1;
	}
	catch (std::exception& ex)
	{
		errorprintf(" setSavePoint failed,std::exception occured,error message: %s\n", ex.what());
		return -1;
	}
	return 0;
}


int MySqlAPI::rollBackToSavePoint(std::string savepointname)
{
	int savepointid = getSavePoint(savepointname);//通过保存点名称，获取对应的序号
	if (savepointid >= 0)//保存点存在	
		return rollBackToSavePoint(savepoint_list[savepointid]);
	warnprintf("savepoint:[%s] not exist, maybe removed by 'commit' or 'rollback' operation earlier by now\n", savepointname.c_str());
	return 0;
}

int MySqlAPI::deleteTableRows(std::vector<int> ids)
{
	try {
		std::string sql = "delete from " +_current_tablename + " where id in (";
		for (int i = 0; i < ids.size(); i++) 
			sql += std::to_string(ids[i]) + ",";
		sql.pop_back();//popback the last ","
		sql += ");";
		int  effectRows = stmt->executeUpdate(sql.c_str());// executeUpdate适用于 UPDATE INSERT  DELETE
		infoprintf("删除数据：%d行\n", effectRows);

		_iscommitted = false;//数据库已修改，但尚未提交
	}
	catch (sql::SQLException& ex)
	{
		errorprintf(" deleteTableRows failed,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		return -1;
	}
	catch (std::exception& ex)
	{
		errorprintf(" deleteTableRows failed,std::exception occured,error message: %s\n", ex.what());
		return -1;
	}
	return 0;
}

int MySqlAPI::commit()
{
	infoprintf( "commit ...\n");
	try {
		releaseAllSavePoints();
		conn->commit();
		infoprintf( "commit OK, all savepoints been relesed\n");
		_iscommitted = true;
	}
	catch (sql::SQLException& ex)
	{
		errorprintf(" commit failed,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		return -1;
	}
	catch (std::exception& ex)
	{
		errorprintf(" commit failed,std::exception occured,error message: %s\n", ex.what());
		return -1;
	}
	return 0;
}

int MySqlAPI::getCurrentTablePropertys() {
	_current_table_col_name.clear();
	_current_table_col_type.clear();
	sql::ResultSet *res = nullptr;
	try
	{
		std::string query = "desc " + _current_tablename;
		res = stmt->executeQuery(query.c_str());
		infoprintf("当前数据表结构:\n");
		while (res->next()) {
			infoprintf("| Field:%15s\t| Type:%15s\t| Null:%4s\t| Key:%4s\t| Default:%4s\t| Extra:%15s |\n", 
				res->getString(1).c_str(), 
				res->getString(2).c_str(), 
				res->getString(3).c_str(), 
				res->getString(4).c_str(),
				res->getString(5).c_str(),
				res->getString(6).c_str());

			_current_table_col_name.push_back(res->getString(1).c_str());
			std::string type = res->getString(2).c_str();

			MysqlDataType mytype = MysqlDataType::UNDEFINED;
			if(type.find("tinyint(") == 0)
				mytype = MysqlDataType::TINYINT;
			else if (type.find("smallint(") == 0)
				mytype = MysqlDataType::SMALLINT;
			else if (type.find("mediumint(") == 0)
				mytype = MysqlDataType::MEDIUMINT;
			else if (type.find("int(") == 0)
				mytype = MysqlDataType::INT;
			else if (type.find("bigint(") == 0)
				mytype = MysqlDataType::BIGINT;
			else if (type.find("float(") == 0)
				mytype = MysqlDataType::FLOAT;
			else if (type.find("double(") == 0)
				mytype = MysqlDataType::DOUBLE;
			else if (type.find("decimal(") == 0)
				mytype = MysqlDataType::DECIMAL;
			else if (type.find("char(") == 0)
				mytype = MysqlDataType::CHAR;
			else if (type.find("varchar(") == 0)
				mytype = MysqlDataType::VARCHAR;
			else if (type.find("date(") == 0)
				mytype = MysqlDataType::DATE;
			else if (type.find("time(") == 0)
				mytype = MysqlDataType::TIME;
			else if (type.find("year(") == 0)
				mytype = MysqlDataType::YEAR;
			else if (type.find("timestamp(") == 0)
				mytype = MysqlDataType::TIMESTAMP;
			else if (type.find("datetime(") == 0)
				mytype = MysqlDataType::DATETIME;
			else if (type.find("datetime(3") == 0)
				mytype = MysqlDataType::DATETIME_MS;
			else if (type.find("tinyblob(") == 0)
				mytype = MysqlDataType::TINYBLOB;
			else if (type.find("blob(") == 0)
				mytype = MysqlDataType::BLOB;
			else if (type.find("mediumblob(") == 0)
				mytype = MysqlDataType::MEDIUMBLOB;
			else if (type.find("longblob(") == 0)
				mytype = MysqlDataType::LONGBLOB;
			else if (type.find("tinytext(") == 0)
				mytype = MysqlDataType::TINYTEXT;
			else if (type.find("text(") == 0)
				mytype = MysqlDataType::TEXT;
			else if (type.find("mediumtext(") == 0)
				mytype = MysqlDataType::MEDIUMTEXT;
			else if (type.find("longtext(") == 0)
				mytype = MysqlDataType::LONGTEXT;
			else 
				mytype = MysqlDataType::UNDEFINED;
			_current_table_col_type.push_back(mytype);
		}
	}
	catch (sql::SQLException& ex)
	{
		delete res;
		errorprintf(" getTableRowCount failed,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		return -1;
	}
	catch (std::exception& ex)
	{
		delete res;
		errorprintf(" getTableRowCount failed,std::exception occured,error message: %s\n", ex.what());
		return -1;
	}

	delete res;
	res = nullptr;

	//getTableRowCount(_current_table_rows);

	return 0;
}


int MySqlAPI::releaseAllSavePoints()
{
	for (int i = savepoint_list.size() - 1; i >= 0; i--)//要按照倒序逐个释放，否则先释放前面的保存点，会使后面的保存点全部自动释放
	{
		try {
			conn->releaseSavepoint(savepoint_list[i]);
		}
		catch (sql::SQLException& ex)
		{
			errorprintf(" delete MySqlAPI failed,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		}
		catch (std::exception& ex)
		{
			errorprintf(" delete MySqlAPI failed,std::exception occured,error message: %s\n", ex.what());
		}
	}
	savepoint_list.clear();
	return 0;
}

mydate MySqlAPI::string_to_date(std::string str)
{
	char *cha = (char*)str.data();             // 将string转换成char*。
	mydate tm_;                                // 定义tm结构体。
	sscanf(cha, "%d-%d-%d", &tm_.year, &tm_.month, &tm_.day);
	return tm_;                                // 返回值。
}


mytime MySqlAPI::string_to_time(std::string str)
{
	char *cha = (char*)str.data();             // 将string转换成char*。
	mytime tm_;                                // 定义tm结构体。
	sscanf(cha, "%d:%d:%d", &tm_.hour, &tm_.minute, &tm_.second);
	return tm_;                                // 返回值。
}

mytime_ms MySqlAPI::string_to_time_ms(std::string str)
{
	char *cha = (char*)str.data();             // 将string转换成char*。
	mytime_ms tm_;                             // 定义tm结构体。
	sscanf(cha, "%d:%d:%d.%d", &tm_.hour, &tm_.minute, &tm_.second, &tm_.millisecond);
	return tm_;                                // 返回值。
}

mydatetime MySqlAPI::string_to_datetime(std::string str)
{
	char *cha = (char*)str.data();             // 将string转换成char*。
	mydatetime tm_;                            // 定义tm结构体。
	sscanf(cha, "%d-%d-%d %d:%d:%d", &tm_.date.year, &tm_.date.month, &tm_.date.day, &tm_.time.hour, &tm_.time.minute, &tm_.time.second);
	return tm_;                                // 返回值。
}

mydatetime_ms MySqlAPI::string_to_datetime_ms(std::string str)
{
	char *cha = (char*)str.data();             // 将string转换成char*。
	mydatetime_ms tm_;                         // 定义tm结构体。
	sscanf(cha, "%d-%d-%d %d:%d:%d.%d", &tm_.date.year, &tm_.date.month, &tm_.date.day, &tm_.time.hour, &tm_.time.minute, &tm_.time.second, &tm_.time.millisecond);
	return tm_;                                // 返回值。
}